﻿using System.Data;
using System.Data.SqlClient;

namespace Infrastructure.Common.Data.Imp
{
    public class SqlHelper
    {
        private static SqlConnection _conn;

        private static SqlDataAdapter _sda;

        private static SqlCommand _sc;

        private readonly string _connectionString;

        public SqlHelper(string dbpath)
        {
            _connectionString = dbpath;
        }


        /// <summary>
        ///     执行sqlserver语句,insert,delete,update
        /// </summary>
        /// <param name="text">sql操作语句</param>
        /// <returns></returns>
        public bool ExecuteCommand(string text)
        {
            try
            {
                using (_conn = new SqlConnection(_connectionString)) //链接数据库
                {
                    //打开数据库
                    _conn.Open();
                    //创建Command对象
                    _sc = _conn.CreateCommand();
                    //设置_sc对象的CommandText的值
                    _sc.CommandText = text;
                    //设置_sda的SelectCommand的属性
                    //_sda.SelectCommand = _sc;          
                    int affectedByTheNumberoFlines = _sc.ExecuteNonQuery();
                    if (affectedByTheNumberoFlines != -1)
                    {
                        _conn.Close();
                        return true;
                    }

                    object reslut = _sc.ExecuteScalar();
                    //关闭链接
                    _conn.Close();
                    return reslut != null;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     执行sqlserver语句
        /// </summary>
        /// <param name="text">sqlserver语句</param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string text)
        {
            try
            {
                //链接数据库
                using (_conn = new SqlConnection(_connectionString))
                {
                    var dataSet = new DataSet();
                    _conn.Open();
                    _sda = new SqlDataAdapter(text, _conn);
                    //将数据库中tableName表的每行属性写到_dataSet缓存中
                    _sda.Fill(dataSet);
                    _sc = _conn.CreateCommand();
                    //设置_sc对象的CommandText的值
                    _sc.CommandText = text;
                    //设置_sda的SelectCommand的属性         
                    object reslut = _sc.ExecuteScalar();
                    //关闭链接
                    _conn.Close();
                    if (reslut != null)
                        return dataSet;
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }
    }
}